package com.hotelManager.www.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.hotelManager.www.po.Shop;
import java.math.BigDecimal;
import com.hotelManager.www.util.JDBCUtils;

public class ShopDao {
    // 修改Shopregister方法，支持事务

    public boolean Shopregister(Shop shop, Connection conn) {
        String sql = "INSERT INTO shop(shopname, address, contact, keywords, status, audit_status, sales_volume, publish_time, revenue, user_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, shop.getShopname());
            ps.setString(2, shop.getAddress());
            ps.setString(3, shop.getContact());
            ps.setString(4, shop.getKeywords());
            ps.setString(5, shop.getStatus());
            ps.setString(6, shop.getAuditStatus());
            ps.setInt(7, shop.getSalesVolume());
            ps.setTimestamp(8, new java.sql.Timestamp(shop.getPublishTime().getTime()));
            ps.setBigDecimal(9, shop.getRevenue());
            ps.setInt(10, shop.getUserId());
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            System.out.println("店铺注册失败：" + e.getMessage());
            e.printStackTrace();
            return false;
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    // 查询所有商家
    public List<Shop> findAll(Connection conn) throws SQLException {
        String sql = "SELECT * FROM shop";
        List<Shop> shops = new ArrayList<>();
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                shops.add(extractShopFromResultSet(rs));
            }
        }
        return shops;
    }

    public Shop findShopBriefById(Integer shopId) throws SQLException {
        String sql = "SELECT id, shopname, address, contact, sales_volume, view_count FROM shop WHERE id = ?";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, shopId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    Shop shop = new Shop();
                    shop.setId(rs.getInt("id"));
                    shop.setShopname(rs.getString("shopname"));
                    shop.setAddress(rs.getString("address"));
                    shop.setContact(rs.getString("contact"));
                    shop.setSalesVolume(rs.getInt("sales_volume"));
                    shop.setViewCount(rs.getInt("view_count"));
                    return shop;
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return null;
    }

    // 根据审核状态查询商家
    public List<Shop> findByAuditStatus(Connection conn, String auditStatus) throws SQLException {
        String sql = "SELECT * FROM shop WHERE audit_status = ? AND status = 'open'";
        List<Shop> shops = new ArrayList<>();
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, auditStatus);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    shops.add(extractShopFromResultSet(rs));
                }
            }
        }
        return shops;
    }

    // 更新商家信息
    public boolean update(Connection conn, Shop shop) throws SQLException {
        String sql = "UPDATE shop SET shopname = ?, address = ?, contact = ?, keywords = ?, " +
                "status = ?, audit_status = ?, sales_volume = ?, publish_time = ?, revenue = ?, user_id = ? WHERE id = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, shop.getShopname());
            stmt.setString(2, shop.getAddress());
            stmt.setString(3, shop.getContact());
            stmt.setString(4, shop.getKeywords());
            stmt.setString(5, shop.getStatus());
            stmt.setString(6, shop.getAuditStatus());
            stmt.setObject(7, shop.getSalesVolume());
            if (shop.getPublishTime() != null) {
                stmt.setTimestamp(8, new java.sql.Timestamp(shop.getPublishTime().getTime()));
            } else {
                stmt.setNull(8, java.sql.Types.TIMESTAMP);
            }
            stmt.setBigDecimal(9, shop.getRevenue());
            stmt.setInt(10, shop.getUserId());
            stmt.setInt(11, shop.getId());
            return stmt.executeUpdate() > 0;
        }
    }

    // 删除商家
    public boolean delete(Connection conn, Integer id) throws SQLException {
        String sql = "DELETE FROM shop WHERE id = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, id);
            return stmt.executeUpdate() > 0;
        }
    }

    // 从ResultSet中提取Shop对象
    public Shop extractShopFromResultSet(ResultSet rs) throws SQLException {
        Shop shop = new Shop();
        shop.setId(rs.getInt("id"));
        shop.setShopname(rs.getString("shopname"));
        shop.setAddress(rs.getString("address"));
        shop.setContact(rs.getString("contact"));
        shop.setKeywords(rs.getString("keywords"));
        shop.setStatus(rs.getString("status"));
        shop.setAuditStatus(rs.getString("audit_status"));
        shop.setSalesVolume(rs.getInt("sales_volume"));
        shop.setPublishTime(rs.getTimestamp("publish_time"));
        shop.setRevenue(rs.getBigDecimal("revenue"));
        shop.setUserId(rs.getInt("user_id"));
        return shop;
    }

    // 按销量排序查询商家
    public List<Shop> findAllOrderBySalesVolume(Connection conn) throws SQLException {
        String sql = "SELECT * FROM shop ORDER BY sales_volume DESC";
        List<Shop> shops = new ArrayList<>();
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                shops.add(extractShopFromResultSet(rs));
            }
        }
        return shops;
    }

    // 按发布时间排序查询商家
    public List<Shop> findAllOrderByPublishTime(Connection conn) throws SQLException {
        String sql = "SELECT * FROM shop ORDER BY publish_time DESC";
        List<Shop> shops = new ArrayList<>();
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                shops.add(extractShopFromResultSet(rs));
            }
        }
        return shops;
    }

    // 根据关键词搜索商家
    public List<Shop> findByKeyword(Connection conn, String keyword) throws SQLException {
        String sql = "SELECT * FROM shop WHERE keywords LIKE ? OR shopname LIKE ?";
        List<Shop> shops = new ArrayList<>();
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            String pattern = "%" + keyword + "%";
            stmt.setString(1, pattern);
            stmt.setString(2, pattern);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    shops.add(extractShopFromResultSet(rs));
                }
            }
        }
        return shops;
    }

    // 分页模糊查询店铺
    public List<Shop> findByKeywordWithPagination(Connection conn, String keyword, int page, int pageSize) throws SQLException {
        String sql = "SELECT * FROM shop WHERE (keywords LIKE ? OR shopname LIKE ?) AND audit_status = 'approved' AND status = 'open' LIMIT ? OFFSET ?";
        List<Shop> shops = new ArrayList<>();
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            String pattern = "%" + keyword + "%";
            stmt.setString(1, pattern);
            stmt.setString(2, pattern);
            stmt.setInt(3, pageSize);
            stmt.setInt(4, (page - 1) * pageSize);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    shops.add(extractShopFromResultSet(rs));
                }
            }
        }
        return shops;
    }
    // 统计符合条件的店铺总数
    public int countByKeyword(Connection conn, String keyword) throws SQLException {
        String sql = "SELECT COUNT(*) FROM shop WHERE (keywords LIKE ? OR shopname LIKE ?) AND audit_status = 'approved' AND status = 'open'";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            String pattern = "%" + keyword + "%";
            stmt.setString(1, pattern);
            stmt.setString(2, pattern);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        }
        return 0;
    }

    // 更新商家销量
    public boolean updateSalesVolume(Integer shopId, Integer count) throws SQLException {
        String sql = "UPDATE shop SET sales_volume = sales_volume + ? WHERE id = ?";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, count);
            stmt.setInt(2, shopId);
            return stmt.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("更新销量失败", e);
        }
    }

    // 根据用户ID查询店铺
    public Shop findByUserId(Integer userId) throws SQLException {
        String sql = "SELECT * FROM shop WHERE user_id = ?";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return extractShopFromResultSet(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("查询用户店铺失败", e);
        }
        return null;
    }

    // 根据店铺名查询店铺
    public Shop findByShopname(Connection conn, String shopname) throws SQLException {
        String sql = "SELECT * FROM shop WHERE shopname = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, shopname);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return extractShopFromResultSet(rs);
                }
            }
        }
        return null;
    }

    // 更新商家营业额
    public boolean updateRevenue(Connection conn, Integer shopId, BigDecimal amount) throws SQLException {
        String sql = "UPDATE shop SET revenue = revenue + ? WHERE id = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setBigDecimal(1, amount);
            stmt.setInt(2, shopId);
            return stmt.executeUpdate() > 0;
        }
    }

    // 添加收藏记录
    public boolean addFavorite(int userId, int shopId) throws SQLException {
        String sql = "INSERT INTO favorites (user_id, shop_id) VALUES (?, ?)";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setInt(2, shopId);
            return stmt.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("添加收藏失败", e);
        }
    }

    // 查询用户收藏的店铺
    public List<Shop> getFavoritesByUserId(int userId) throws SQLException {
        String sql = "SELECT s.* FROM shop s JOIN favorites f ON s.id = f.shop_id WHERE f.user_id = ?";
        List<Shop> favoriteShops = new ArrayList<>();
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    favoriteShops.add(extractShopFromResultSet(rs));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取收藏店铺失败", e);
        }
        return favoriteShops;
    }

    // 更新收藏量
    public boolean updateFavoritesVolume(int shopId, int change) throws SQLException {
        String sql = "UPDATE shop SET favorite_volume = favorite_volume + ? WHERE id = ?";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, change);
            pstmt.setInt(2, shopId);
            System.out.println("调用了 addFavoriteShop 方法");
            System.out.println("调用了 updateFavoritesVolume 方法");
            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("更新收藏量失败", e);
        }
    }

    // 删除收藏记录


    // 获取所有未被拉黑的商家
    public List<Shop> getAllShopsExceptBlacklisted(Connection conn, int userId) throws SQLException {
        String sql = "SELECT * FROM shop s " +
                    "WHERE NOT EXISTS (SELECT 1 FROM shop_blacklist bl " +
                    "WHERE bl.user_id = ? AND bl.shop_id = s.id)";
        List<Shop> shops = new ArrayList<>();
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    shops.add(extractShopFromResultSet(rs));
                }
            }
        }
        return shops;
    }

    // 根据条件搜索未被拉黑的商家
    public List<Shop> searchShopsExceptBlacklisted(Connection conn, int userId, String keyword, 
            String location, BigDecimal minPrice, BigDecimal maxPrice) throws SQLException {
        StringBuilder sql = new StringBuilder(
            "SELECT * FROM shop s WHERE NOT EXISTS " +
            "(SELECT 1 FROM shop_blacklist bl WHERE bl.user_id = ? AND bl.shop_id = s.id)");
        
        List<Object> params = new ArrayList<>();
        params.add(userId);
        
        if (keyword != null && !keyword.trim().isEmpty()) {
            sql.append(" AND (s.name LIKE ? OR s.description LIKE ?)");
            params.add("%" + keyword + "%");
            params.add("%" + keyword + "%");
        }
        
        if (location != null && !location.trim().isEmpty()) {
            sql.append(" AND s.location LIKE ?");
            params.add("%" + location + "%");
        }
        
        if (minPrice != null) {
            sql.append(" AND s.price >= ?");
            params.add(minPrice);
        }
        
        if (maxPrice != null) {
            sql.append(" AND s.price <= ?");
            params.add(maxPrice);
        }

        List<Shop> shops = new ArrayList<>();
        try (PreparedStatement stmt = conn.prepareStatement(sql.toString())) {
            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    shops.add(extractShopFromResultSet(rs));
                }
            }
        }
        return shops;
    }

    // 获取单个未被拉黑的商家详情
    public Shop getShopByIdExceptBlacklisted(Connection conn, int userId, int shopId) throws SQLException {
        String sql = "SELECT * FROM shop s " +
                    "WHERE s.id = ? AND NOT EXISTS " +
                    "(SELECT 1 FROM shop_blacklist bl WHERE bl.user_id = ? AND bl.shop_id = s.id)";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, shopId);
            stmt.setInt(2, userId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return extractShopFromResultSet(rs);
                }
            }
        }
        return null;
    }

    // 根据商家ID查询商家详情
    public Shop findById(int shopId) throws Exception {
        System.out.println("[ShopDao] 查询店铺, shopId=" + shopId);
        try (Connection conn = JDBCUtils.getConnection()) {
            String sql = "SELECT * FROM shop WHERE id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setInt(1, shopId);
                try (ResultSet rs = stmt.executeQuery()) {
                    if (rs.next()) {
                        Shop shop = extractShopFromResultSet(rs);
                        System.out.println("[ShopDao] 查询到店铺: " + shop);
                        return shop;
                    }
                    System.out.println("[ShopDao] 未找到店铺");
                    return null;
                }
            }
        } catch (Exception e) {
            System.out.println("[ShopDao] 查询店铺异常: " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
    }

    // 更新店铺浏览量
    public boolean updateViewCount(Integer shopId) throws SQLException {
        String sql = "UPDATE shop SET view_count = view_count + 1 WHERE id = ?";
        try (Connection conn = JDBCUtils.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, shopId);
            return stmt.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("更新浏览量失败", e);
        }
    }

    // 查询所有可见商家
    public List<Shop> findAllVisibleShops(Connection conn) throws SQLException {
        String sql = "SELECT * FROM shop WHERE status = 'open'";
        List<Shop> shops = new ArrayList<>();
        try (PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                shops.add(extractShopFromResultSet(rs));
            }
        }
        return shops;
    }

    /**
     * 删除店铺时，手动删除关联的浏览历史记录。
     * @param conn 数据库连接
     * @param shopId 店铺ID
     * @return 删除是否成功
     */
    public boolean deleteShopAndRelatedHistory(Connection conn, int shopId) throws SQLException {
        // 删除关联的浏览历史记录
        String deleteHistorySql = "DELETE FROM browse_history WHERE shop_id = ?";
        try (PreparedStatement deleteHistoryStmt = conn.prepareStatement(deleteHistorySql)) {
            deleteHistoryStmt.setInt(1, shopId);
            deleteHistoryStmt.executeUpdate();
        }

        // 删除店铺
        String deleteShopSql = "DELETE FROM shop WHERE id = ?";
        try (PreparedStatement deleteShopStmt = conn.prepareStatement(deleteShopSql)) {
            deleteShopStmt.setInt(1, shopId);
            return deleteShopStmt.executeUpdate() > 0;
        }
    }
    public List<Shop> findByKeywordWithPaginationAndSort(Connection conn, String keyword, int page, int pageSize, String sortField, String sortOrder) throws SQLException {
        // 安全性校验，防止SQL注入
        String orderBy = "sales_volume";
        if ("publish_time".equals(sortField)) orderBy = "publish_time";
        String order = "ASC";
        if ("desc".equalsIgnoreCase(sortOrder)) order = "DESC";
        String sql = "SELECT * FROM shop WHERE (keywords LIKE ? OR shopname LIKE ?) AND audit_status = 'approved' AND status = 'open' ORDER BY " + orderBy + " " + order + " LIMIT ? OFFSET ?";
        List<Shop> shops = new ArrayList<>();
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            String pattern = "%" + keyword + "%";
            stmt.setString(1, pattern);
            stmt.setString(2, pattern);
            stmt.setInt(3, pageSize);
            stmt.setInt(4, (page - 1) * pageSize);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    shops.add(extractShopFromResultSet(rs));
                }
            }
        }
        return shops;
    }
}

